package com.weijian.service.impl;

import com.weijian.common.exception.SqlExecutionException;
import com.weijian.dto.data.ColumnInfo;
import com.weijian.dto.data.QueryResult;
import com.weijian.dto.data.TableInfo;
import com.weijian.dto.data.TableStructure;
import com.weijian.service.AnalysisDataSourceService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

@Service
@Slf4j
public class AnalysisDataSourceServiceImpl implements AnalysisDataSourceService {

    private final JdbcTemplate jdbcTemplate;

    public AnalysisDataSourceServiceImpl(@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        log.info("AnalysisDataSourceService 初始化完成");
    }

    /**
     * 执行SQL查询
     *
     * @param sql SQL查询语句
     * @return 查询结果
     * @throws SqlExecutionException SQL执行异常
     */
    @Override
    public QueryResult executeQuery(String sql) throws SqlExecutionException {
        return executeQuery(sql, new Object[0]);
    }

    /**
     * 执行带参数的SQL查询
     *
     * @param sql    SQL查询语句
     * @param params SQL参数
     * @return 查询结果
     * @throws SqlExecutionException SQL执行异常
     */
    @Override
    public QueryResult executeQuery(String sql, Object... params) throws SqlExecutionException {
        // 参数验证
        if (sql == null || sql.trim().isEmpty()) {
            throw new SqlExecutionException("SQL语句不能为空");
        }

        log.debug("执行SQL查询: {}", sql);
        if (params != null && params.length > 0) {
            log.debug("查询参数: {}", (Object) params);
        }

        long startTime = System.currentTimeMillis();

        try {
            // 执行查询
            List<Map<String, Object>> rows = params != null && params.length > 0
                    ? jdbcTemplate.queryForList(sql, params)
                    : jdbcTemplate.queryForList(sql);

            // 提取列名
            List<String> columns = extractColumns(rows);

            // 计算执行时间
            long executionTime = System.currentTimeMillis() - startTime;

            log.info("SQL执行完成 - 耗时: {}ms, 返回行数: {}", executionTime, rows.size());

            // 构建并返回查询结果
            return QueryResult.builder()
                    .columns(columns)
                    .rows(rows)
                    .rowCount(rows.size())
                    .executionTime(executionTime)
                    .build();

        } catch (DataAccessException e) {
            long executionTime = System.currentTimeMillis() - startTime;
            log.error("SQL执行失败 - SQL: {}, 耗时: {}ms, 错误: {}", sql, executionTime, e.getMessage());
            throw new SqlExecutionException("SQL执行失败: " + e.getMessage(), e);
        } catch (Exception e) {
            long executionTime = System.currentTimeMillis() - startTime;
            log.error("SQL执行发生未知错误 - SQL: {}, 耗时: {}ms", sql, executionTime, e);
            throw new SqlExecutionException("SQL执行发生未知错误: " + e.getMessage(), e);
        }
    }

    /**
     * 测试数据库连接
     *
     * @return 连接是否正常
     */
    @Override
    public boolean testConnection() {
        try {
            log.debug("测试数据库连接...");
            jdbcTemplate.queryForObject("SELECT 1", Integer.class);
            log.info("数据库连接测试成功");
            return true;
        } catch (Exception e) {
            log.error("数据库连接测试失败: {}", e.getMessage());
            return false;
        }
    }

    /**
     * 获取数据库中所有表的列表
     *
     * @return 表信息列表
     * @throws SqlExecutionException 查询失败时抛出
     */
    @Override
    public List<TableInfo> getTables() throws SqlExecutionException {
        log.info("查询数据库表列表");

        try {
            String sql = """
                    SELECT
                        t.tablename AS table_name,
                        obj_description((quote_ident(t.schemaname) || '.' || quote_ident(t.tablename))::regclass, 'pg_class') AS table_comment,
                        t.schemaname AS schema_name,
                        'TABLE' AS table_type
                    FROM pg_tables t
                    WHERE t.schemaname = 'public'
                    ORDER BY t.tablename
                    """;

            List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);

            List<TableInfo> tables = new ArrayList<>();
            for (Map<String, Object> row : rows) {
                String tableName = (String) row.get("table_name");

                // 查询表的行数
                Long rowCount = getTableRowCount(tableName);

                TableInfo tableInfo = TableInfo.builder()
                        .tableName(tableName)
                        .tableComment((String) row.get("table_comment"))
                        .schemaName((String) row.get("schema_name"))
                        .tableType((String) row.get("table_type"))
                        .rowCount(rowCount)
                        .build();

                tables.add(tableInfo);
            }

            log.info("查询到 {} 张表", tables.size());
            return tables;

        } catch (DataAccessException e) {
            log.error("查询表列表失败: {}", e.getMessage());
            throw new SqlExecutionException("查询表列表失败: " + e.getMessage(), e);
        }
    }

    /**
     * 获取指定表的结构信息
     *
     * @param tableName 表名
     * @return 表结构信息
     * @throws SqlExecutionException 查询失败时抛出
     */
    @Override
    public TableStructure getTableStructure(String tableName) throws SqlExecutionException {
        log.info("查询表结构: {}", tableName);

        try {
            // 查询表信息
            TableInfo tableInfo = getTableInfo(tableName);

            // 查询列信息
            List<ColumnInfo> columns = getTableColumns(tableName);

            TableStructure structure = TableStructure.builder()
                    .tableInfo(tableInfo)
                    .columns(columns)
                    .build();

            log.info("查询表结构成功: {}, 列数: {}", tableName, columns.size());
            return structure;

        } catch (DataAccessException e) {
            log.error("查询表结构失败: {}, 错误: {}", tableName, e.getMessage());
            throw new SqlExecutionException("查询表结构失败: " + e.getMessage(), e);
        }
    }

    /**
     * 获取表的基本信息
     *
     * @param tableName 表名
     * @return 表信息
     */
    private TableInfo getTableInfo(String tableName) {
        String sql = """
                SELECT
                    t.tablename AS table_name,
                    obj_description((quote_ident(t.schemaname) || '.' || quote_ident(t.tablename))::regclass, 'pg_class') AS table_comment,
                    t.schemaname AS schema_name,
                    'TABLE' AS table_type
                FROM pg_tables t
                WHERE t.schemaname = 'public' AND t.tablename = ?
                """;

        Map<String, Object> row = jdbcTemplate.queryForMap(sql, tableName);
        Long rowCount = getTableRowCount(tableName);

        return TableInfo.builder()
                .tableName((String) row.get("table_name"))
                .tableComment((String) row.get("table_comment"))
                .schemaName((String) row.get("schema_name"))
                .tableType((String) row.get("table_type"))
                .rowCount(rowCount)
                .build();
    }


    /**
     * 从查询结果中提取列名
     *
     * @param rows 查询结果行
     * @return 列名列表
     */
    private List<String> extractColumns(List<Map<String, Object>> rows) {
        if (rows == null || rows.isEmpty()) {
            return Collections.emptyList();
        }

        // 从第一行数据中提取列名
        Map<String, Object> firstRow = rows.get(0);
        return new ArrayList<>(firstRow.keySet());
    }

    /**
     * 获取表的行数
     *
     * @param tableName 表名
     * @return 行数
     */
    private Long getTableRowCount(String tableName) {
        try {
            String sql = "SELECT COUNT(*) FROM " + tableName;
            return jdbcTemplate.queryForObject(sql, Long.class);
        } catch (Exception e) {
            log.warn("获取表 {} 的行数失败: {}", tableName, e.getMessage());
            return null;
        }
    }

    /**
     * 获取表的列信息
     *
     * @param tableName 表名
     * @return 列信息列表
     */
    private List<ColumnInfo> getTableColumns(String tableName) {
        String sql = """
                SELECT
                    c.column_name,
                    c.data_type,
                    c.is_nullable,
                    c.column_default,
                    c.ordinal_position,
                    c.character_maximum_length,
                    col_description((quote_ident(c.table_schema) || '.' || quote_ident(c.table_name))::regclass, c.ordinal_position) AS column_comment,
                    CASE WHEN pk.column_name IS NOT NULL THEN true ELSE false END AS is_primary_key
                FROM information_schema.columns c
                LEFT JOIN (
                    SELECT ku.column_name
                    FROM information_schema.table_constraints tc
                    JOIN information_schema.key_column_usage ku
                        ON tc.constraint_name = ku.constraint_name
                        AND tc.table_schema = ku.table_schema
                    WHERE tc.constraint_type = 'PRIMARY KEY'
                        AND tc.table_schema = 'public'
                        AND tc.table_name = ?
                ) pk ON c.column_name = pk.column_name
                WHERE c.table_schema = 'public' AND c.table_name = ?
                ORDER BY c.ordinal_position
                """;

        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, tableName, tableName);

        List<ColumnInfo> columns = new ArrayList<>();
        for (Map<String, Object> row : rows) {
            ColumnInfo columnInfo = ColumnInfo.builder()
                    .columnName((String) row.get("column_name"))
                    .dataType((String) row.get("data_type"))
                    .nullable("YES".equalsIgnoreCase((String) row.get("is_nullable")))
                    .defaultValue((String) row.get("column_default"))
                    .ordinalPosition(((Number) row.get("ordinal_position")).intValue())
                    .characterMaximumLength(row.get("character_maximum_length") != null
                            ? ((Number) row.get("character_maximum_length")).intValue()
                            : null)
                    .columnComment((String) row.get("column_comment"))
                    .primaryKey((Boolean) row.get("is_primary_key"))
                    .build();

            columns.add(columnInfo);
        }

        return columns;
    }

}
